<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.01 Transitional//EN" "http://www.w3.org/TR/html4/loose.dtd">
<html lang="en-US">
<head>
<!-- GenHTML revision 25226-->
<meta http-equiv="Content-type" content="text/html; charset=utf-8">
<title>Example Queries - The Java EE 6 Tutorial</title>
<meta name="robots" content="index,follow">
<meta name="robots" content="index,follow">
<meta name="date" content="2011-03-01">
<link rel="stylesheet" type="text/css" href="css/default.css">
<link rel="stylesheet" type="text/css" href="css/ipg.css">
<link rel="stylesheet" type="text/css" href="css/javaeetutorial.css">
</head>

<body>

<table border="0" cellpadding="5" cellspacing="0" width="100%">
<tbody>
   <tr valign="top">
      <td width="400px"><p class="toc level1"><a href="docinfo.html">Document Information</a></p>
<p class="toc level1 tocsp"><a href="gexaf.html">Preface</a></p>
<p class="toc level1 tocsp"><a href="gfirp.html">Part&nbsp;I&nbsp;Introduction</a></p>
<p class="toc level2"><a href="bnaaw.html">1.&nbsp;&nbsp;Overview</a></p>
<p class="toc level2"><a href="gfiud.html">2.&nbsp;&nbsp;Using the Tutorial Examples</a></p>
<p class="toc level1 tocsp"><a href="bnadp.html">Part&nbsp;II&nbsp;The Web Tier</a></p>
<p class="toc level2"><a href="bnadr.html">3.&nbsp;&nbsp;Getting Started with Web Applications</a></p>
<p class="toc level2"><a href="bnaph.html">4.&nbsp;&nbsp;JavaServer Faces Technology</a></p>
<p class="toc level2"><a href="giepx.html">5.&nbsp;&nbsp;Introduction to Facelets</a></p>
<p class="toc level2"><a href="gjddd.html">6.&nbsp;&nbsp;Expression Language</a></p>
<p class="toc level2"><a href="bnaqz.html">7.&nbsp;&nbsp;Using JavaServer Faces Technology in Web Pages</a></p>
<p class="toc level2"><a href="gjcut.html">8.&nbsp;&nbsp;Using Converters, Listeners, and Validators</a></p>
<p class="toc level2"><a href="bnatx.html">9.&nbsp;&nbsp;Developing with JavaServer Faces Technology</a></p>
<p class="toc level2"><a href="gkmaa.html">10.&nbsp;&nbsp;JavaServer Faces Technology Advanced Concepts</a></p>
<p class="toc level2"><a href="bnawo.html">11.&nbsp;&nbsp;Configuring JavaServer Faces Applications</a></p>
<p class="toc level2"><a href="gkiow.html">12.&nbsp;&nbsp;Using Ajax with JavaServer Faces Technology</a></p>
<p class="toc level2"><a href="gkhxa.html">13.&nbsp;&nbsp;Advanced Composite Components</a></p>
<p class="toc level2"><a href="bnavg.html">14.&nbsp;&nbsp;Creating Custom UI Components</a></p>
<p class="toc level2"><a href="bnafd.html">15.&nbsp;&nbsp;Java Servlet Technology</a></p>
<p class="toc level2"><a href="bnaxu.html">16.&nbsp;&nbsp;Internationalizing and Localizing Web Applications</a></p>
<p class="toc level1 tocsp"><a href="bnayk.html">Part&nbsp;III&nbsp;Web Services</a></p>
<p class="toc level2"><a href="gijti.html">17.&nbsp;&nbsp;Introduction to Web Services</a></p>
<p class="toc level2"><a href="bnayl.html">18.&nbsp;&nbsp;Building Web Services with JAX-WS</a></p>
<p class="toc level2"><a href="giepu.html">19.&nbsp;&nbsp;Building RESTful Web Services with JAX-RS</a></p>
<p class="toc level2"><a href="gjjxe.html">20.&nbsp;&nbsp;Advanced JAX-RS Features</a></p>
<p class="toc level2"><a href="gkojl.html">21.&nbsp;&nbsp;Running the Advanced JAX-RS Example Application</a></p>
<p class="toc level1 tocsp"><a href="bnblr.html">Part&nbsp;IV&nbsp;Enterprise Beans</a></p>
<p class="toc level2"><a href="gijsz.html">22.&nbsp;&nbsp;Enterprise Beans</a></p>
<p class="toc level2"><a href="gijre.html">23.&nbsp;&nbsp;Getting Started with Enterprise Beans</a></p>
<p class="toc level2"><a href="gijrb.html">24.&nbsp;&nbsp;Running the Enterprise Bean Examples</a></p>
<p class="toc level2"><a href="bnbpk.html">25.&nbsp;&nbsp;A Message-Driven Bean Example</a></p>
<p class="toc level2"><a href="gkcqz.html">26.&nbsp;&nbsp;Using the Embedded Enterprise Bean Container</a></p>
<p class="toc level2"><a href="gkidz.html">27.&nbsp;&nbsp;Using Asynchronous Method Invocation in Session Beans</a></p>
<p class="toc level1 tocsp"><a href="gjbnr.html">Part&nbsp;V&nbsp;Contexts and Dependency Injection for the Java EE Platform</a></p>
<p class="toc level2"><a href="giwhb.html">28.&nbsp;&nbsp;Introduction to Contexts and Dependency Injection for the Java EE Platform</a></p>
<p class="toc level2"><a href="gjbls.html">29.&nbsp;&nbsp;Running the Basic Contexts and Dependency Injection Examples</a></p>
<p class="toc level2"><a href="gjehi.html">30.&nbsp;&nbsp;Contexts and Dependency Injection for the Java EE Platform: Advanced Topics</a></p>
<p class="toc level2"><a href="gkhre.html">31.&nbsp;&nbsp;Running the Advanced Contexts and Dependency Injection Examples</a></p>
<p class="toc level1 tocsp"><a href="bnbpy.html">Part&nbsp;VI&nbsp;Persistence</a></p>
<p class="toc level2"><a href="bnbpz.html">32.&nbsp;&nbsp;Introduction to the Java Persistence API</a></p>
<p class="toc level2"><a href="gijst.html">33.&nbsp;&nbsp;Running the Persistence Examples</a></p>
<p class="toc level2"><a href="bnbtg.html">34.&nbsp;&nbsp;The Java Persistence Query Language</a></p>
<p class="toc level3"><a href="bnbth.html">Query Language Terminology</a></p>
<p class="toc level3"><a href="bnbrg.html">Creating Queries Using the Java Persistence Query Language</a></p>
<p class="toc level4"><a href="bnbrg.html#bnbrh">Named Parameters in Queries</a></p>
<p class="toc level4"><a href="bnbrg.html#bnbri">Positional Parameters in Queries</a></p>
<p class="toc level3 tocsp"><a href="bnbti.html">Simplified Query Language Syntax</a></p>
<p class="toc level4"><a href="bnbti.html#bnbtj">Select Statements</a></p>
<p class="toc level4"><a href="bnbti.html#bnbtk">Update and Delete Statements</a></p>
<div id="scrolltoc" class="onpage">
<p class="toc level3 tocsp"><a href="">Example Queries</a></p>
<p class="toc level4"><a href="#bnbtm">Simple Queries</a></p>
<p class="toc level5"><a href="#bnbtn">A Basic Select Query</a></p>
<p class="toc level5"><a href="#bnbto">Eliminating Duplicate Values</a></p>
<p class="toc level5"><a href="#bnbtp">Using Named Parameters</a></p>
<p class="toc level4 tocsp"><a href="#bnbtq">Queries That Navigate to Related Entities</a></p>
<p class="toc level5"><a href="#bnbtr">A Simple Query with Relationships</a></p>
<p class="toc level5"><a href="#bnbts">Navigating to Single-Valued Relationship Fields</a></p>
<p class="toc level5"><a href="#bnbtt">Traversing Relationships with an Input Parameter</a></p>
<p class="toc level5"><a href="#bnbtu">Traversing Multiple Relationships</a></p>
<p class="toc level5"><a href="#bnbtv">Navigating According to Related Fields</a></p>
<p class="toc level4 tocsp"><a href="#bnbtw">Queries with Other Conditional Expressions</a></p>
<p class="toc level5"><a href="#bnbtx">The <tt>LIKE</tt> Expression</a></p>
<p class="toc level5"><a href="#bnbty">The <tt>IS NULL</tt> Expression</a></p>
<p class="toc level5"><a href="#bnbtz">The <tt>IS EMPTY</tt> Expression</a></p>
<p class="toc level5"><a href="#bnbua">The <tt>BETWEEN</tt> Expression</a></p>
<p class="toc level5"><a href="#bnbub">Comparison Operators</a></p>
<p class="toc level4 tocsp"><a href="#bnbuc">Bulk Updates and Deletes</a></p>
<p class="toc level5"><a href="#bnbud">Update Queries</a></p>
<p class="toc level5"><a href="#bnbue">Delete Queries</a></p>
</div>
<p class="toc level3 tocsp"><a href="bnbuf.html">Full Query Language Syntax</a></p>
<p class="toc level4"><a href="bnbuf.html#bnbug">BNF Symbols</a></p>
<p class="toc level4"><a href="bnbuf.html#bnbui">BNF Grammar of the Java Persistence Query Language</a></p>
<p class="toc level4"><a href="bnbuf.html#bnbuj"><tt>FROM</tt> Clause</a></p>
<p class="toc level5"><a href="bnbuf.html#bnbuk">Identifiers</a></p>
<p class="toc level5"><a href="bnbuf.html#bnbum">Identification Variables</a></p>
<p class="toc level5"><a href="bnbuf.html#bnbun">Range Variable Declarations</a></p>
<p class="toc level5"><a href="bnbuf.html#bnbuo">Collection Member Declarations</a></p>
<p class="toc level5"><a href="bnbuf.html#bnbup">Joins</a></p>
<p class="toc level4 tocsp"><a href="bnbuf.html#bnbuq">Path Expressions</a></p>
<p class="toc level5"><a href="bnbuf.html#bnbur">Examples of Path Expressions</a></p>
<p class="toc level5"><a href="bnbuf.html#bnbus">Expression Types</a></p>
<p class="toc level5"><a href="bnbuf.html#bnbut">Navigation</a></p>
<p class="toc level4 tocsp"><a href="bnbuf.html#bnbuu"><tt>WHERE</tt> Clause</a></p>
<p class="toc level5"><a href="bnbuf.html#bnbuv">Literals</a></p>
<p class="toc level5"><a href="bnbuf.html#bnbva">Input Parameters</a></p>
<p class="toc level5"><a href="bnbuf.html#bnbvb">Conditional Expressions</a></p>
<p class="toc level5"><a href="bnbuf.html#bnbvc">Operators and Their Precedence</a></p>
<p class="toc level5"><a href="bnbuf.html#bnbve"><tt>BETWEEN</tt> Expressions</a></p>
<p class="toc level5"><a href="bnbuf.html#bnbvf"><tt>IN</tt> Expressions</a></p>
<p class="toc level5"><a href="bnbuf.html#bnbvg"><tt>LIKE</tt> Expressions</a></p>
<p class="toc level5"><a href="bnbuf.html#bnbvi"><tt>NULL</tt> Comparison Expressions</a></p>
<p class="toc level5"><a href="bnbuf.html#bnbvj">Empty Collection Comparison Expressions</a></p>
<p class="toc level5"><a href="bnbuf.html#bnbvk">Collection Member Expressions</a></p>
<p class="toc level5"><a href="bnbuf.html#bnbvl">Subqueries</a></p>
<p class="toc level5"><a href="bnbuf.html#bnbvo">Functional Expressions</a></p>
<p class="toc level5"><a href="bnbuf.html#gjjnd">Case Expressions</a></p>
<p class="toc level5"><a href="bnbuf.html#bnbvr">NULL Values</a></p>
<p class="toc level5"><a href="bnbuf.html#bnbvu">Equality Semantics</a></p>
<p class="toc level4 tocsp"><a href="bnbuf.html#bnbvx"><tt>SELECT</tt> Clause</a></p>
<p class="toc level5"><a href="bnbuf.html#bnbvy">Return Types</a></p>
<p class="toc level5"><a href="bnbuf.html#bnbwb">The <tt>DISTINCT</tt> Keyword</a></p>
<p class="toc level5"><a href="bnbuf.html#bnbwc">Constructor Expressions</a></p>
<p class="toc level4 tocsp"><a href="bnbuf.html#bnbwd"><tt>ORDER BY</tt> Clause</a></p>
<p class="toc level4"><a href="bnbuf.html#bnbwe"><tt>GROUP BY</tt> and <tt>HAVING</tt> Clauses</a></p>
<p class="toc level2 tocsp"><a href="gjitv.html">35.&nbsp;&nbsp;Using the Criteria API to Create Queries</a></p>
<p class="toc level2"><a href="gkjiq.html">36.&nbsp;&nbsp;Creating and Using String-Based Criteria Queries</a></p>
<p class="toc level2"><a href="gkjjf.html">37.&nbsp;&nbsp;Controlling Concurrent Access to Entity Data with Locking</a></p>
<p class="toc level2"><a href="gkjia.html">38.&nbsp;&nbsp;Improving the Performance of Java Persistence API Applications By Setting a Second-Level Cache</a></p>
<p class="toc level1 tocsp"><a href="gijrp.html">Part&nbsp;VII&nbsp;Security</a></p>
<p class="toc level2"><a href="bnbwj.html">39.&nbsp;&nbsp;Introduction to Security in the Java EE Platform</a></p>
<p class="toc level2"><a href="bncas.html">40.&nbsp;&nbsp;Getting Started Securing Web Applications</a></p>
<p class="toc level2"><a href="bnbyk.html">41.&nbsp;&nbsp;Getting Started Securing Enterprise Applications</a></p>
<p class="toc level1 tocsp"><a href="gijue.html">Part&nbsp;VIII&nbsp;Java EE Supporting Technologies</a></p>
<p class="toc level2"><a href="gijto.html">42.&nbsp;&nbsp;Introduction to Java EE Supporting Technologies</a></p>
<p class="toc level2"><a href="bncih.html">43.&nbsp;&nbsp;Transactions</a></p>
<p class="toc level2"><a href="bncjh.html">44.&nbsp;&nbsp;Resource Connections</a></p>
<p class="toc level2"><a href="bncdq.html">45.&nbsp;&nbsp;Java Message Service Concepts</a></p>
<p class="toc level2"><a href="bncgv.html">46.&nbsp;&nbsp;Java Message Service Examples</a></p>
<p class="toc level2"><a href="gkahp.html">47.&nbsp;&nbsp;Advanced Bean Validation Concepts and Examples</a></p>
<p class="toc level2"><a href="gkeed.html">48.&nbsp;&nbsp;Using Java EE Interceptors</a></p>
<p class="toc level1 tocsp"><a href="gkgjw.html">Part&nbsp;IX&nbsp;Case Studies</a></p>
<p class="toc level2"><a href="gkaee.html">49.&nbsp;&nbsp;Duke's Tutoring Case Study Example</a></p>
<p class="toc level1 tocsp"><a href="idx-1.html">Index</a></p>
</td>
      <td width="10px">&nbsp;</td>
      <td>
         <div class="header">
             <div class="banner">
                <table width="100%" border="0" cellpadding="5" cellspacing="0">
                   <tbody>
                      <tr>
                         <td valign="bottom"><p class="Banner">The Java EE 6 Tutorial
</p></td>
                         <td align="right"  valign="bottom"><img src="graphics/javalogo.png" alt="Java Coffee Cup logo"></td>
                      </tr>
                   </tbody>
                </table>
             </div>

             <div class="header-links">
	         <a href="./index.html">Home</a> | 
<a href="../information/download.html">Download</a> | 
<a href="./javaeetutorial6.pdf">PDF</a> | 
<a href="../information/faq.html">FAQ</a> | 
<a href="http://download.oracle.com/javaee/feedback.htm">Feedback</a>

             </div>
             <div class="navigation">
                 <a href="bnbti.html"><img src="graphics/leftButton.gif" border="0" alt="Previous" title="Previous"></a>
                 <a href="p1.html"><img src="graphics/upButton.gif" border="0" alt="Contents" title="Contents"></a>
                 <a href="bnbuf.html"><img src="graphics/rightButton.gif" border="0" alt="Next" title="Next"></a>
             </div>
         </div>

	 <div class="maincontent">      	 
             

<a name="bnbtl"></a><h2>Example Queries</h2>
<a name="indexterm-1739"></a><a name="indexterm-1740"></a><p>The following queries are from the <tt>Player</tt> entity of the <tt>roster</tt> application, which
is documented in <a href="giqsq.html">The <tt>roster</tt> Application</a>.</p>



<a name="bnbtm"></a><h3>Simple Queries</h3>
<p>If you are unfamiliar with the query language, these simple queries are a
good place to start.</p>



<a name="bnbtn"></a><h4>A Basic Select Query</h4>
<pre>SELECT p
FROM Player p</pre>
<ul><li><p><b>Data retrieved</b>: All players.</p>

</li>
<li><p><b>Description</b>: The <tt>FROM</tt> clause declares an identification variable named <tt>p</tt>, omitting the optional keyword <tt>AS</tt>. If the <tt>AS</tt> keyword were included, the clause would be written as follows:</p>

<pre>FROM Player AS
 p</pre><p>The <tt>Player</tt> element is the abstract schema name of the <tt>Player</tt> entity.</p>

</li>
<li><p><b>See also</b>: <a href="bnbuf.html#bnbum">Identification Variables</a>.</p>

</li></ul>


<a name="bnbto"></a><h4>Eliminating Duplicate Values</h4>
<pre>SELECT DISTINCT
 p
FROM Player p
WHERE p.position = ?1</pre>
<ul><li><p><a name="indexterm-1741"></a><b>Data retrieved</b>: The players with the position specified by the query&rsquo;s parameter.</p>

</li>
<li><p><a name="indexterm-1742"></a><a name="indexterm-1743"></a><b>Description</b>: The <tt>DISTINCT</tt> keyword eliminates duplicate values.</p>

<p>The <tt>WHERE</tt> clause restricts the players retrieved by checking their <tt>position</tt>, a persistent field of the <tt>Player</tt> entity. The <tt>?1</tt> element denotes the input parameter of the query.</p>

</li>
<li><p><b>See also</b>: <a href="bnbuf.html#bnbva">Input Parameters</a> and <a href="bnbuf.html#bnbwb">The <tt>DISTINCT</tt> Keyword</a>.</p>

</li></ul>


<a name="bnbtp"></a><h4>Using Named Parameters</h4>
<pre>SELECT DISTINCT p
FROM Player p
WHERE p.position = :position AND p.name = :name</pre>
<ul><li><p><b>Data retrieved</b>: The players having the specified positions and names.</p>

</li>
<li><p><a name="indexterm-1744"></a><a name="indexterm-1745"></a><b>Description</b>: The <tt>position</tt> and <tt>name</tt> elements are persistent fields of the <tt>Player</tt> entity. The <tt>WHERE</tt> clause compares the values of these fields with the named parameters of the query, set using the <tt>Query.setNamedParameter</tt> method. The query language denotes a named input parameter using a colon (<tt>:</tt>) followed by an identifier. The first input parameter is <tt>:position</tt>, the second is <tt>:name</tt>.</p>

</li></ul>


<a name="bnbtq"></a><h3>Queries That Navigate to Related Entities</h3>
<a name="indexterm-1746"></a><p><a name="indexterm-1747"></a><a name="indexterm-1748"></a>In the query language, an expression can traverse, or navigate, to related entities.
These expressions are the primary difference between the Java Persistence query language and SQL.
Queries navigates to related entities, whereas SQL joins tables.</p>



<a name="bnbtr"></a><h4>A Simple Query with Relationships</h4>
<pre>SELECT DISTINCT p
FROM Player p, IN(p.teams) t</pre>
<ul><li><p><b>Data retrieved</b>: All players who belong to a team.</p>

</li>
<li><p><a name="indexterm-1749"></a><a name="indexterm-1750"></a><b>Description</b>: The <tt>FROM</tt> clause declares two identification variables: <tt>p</tt> and <tt>t</tt>. The <tt>p</tt> variable represents the <tt>Player</tt> entity, and the <tt>t</tt> variable represents the related <tt>Team</tt> entity. The declaration for <tt>t</tt> references the previously declared <tt>p</tt> variable. The <tt>IN</tt> keyword signifies that <tt>teams</tt> is a collection of related entities. The <tt>p.teams</tt> expression navigates from a <tt>Player</tt> to its related <tt>Team</tt>. The period in the <tt>p.teams</tt> expression is the navigation operator.</p>

<p><a name="indexterm-1751"></a>You may also use the <tt>JOIN</tt> statement to write the same query:</p>

<pre>SELECT DISTINCT p
FROM Player p JOIN p.teams t</pre><p>This query could also be rewritten as:</p>

<pre>SELECT DISTINCT p
FROM Player p
WHERE p.team IS NOT EMPTY</pre></li></ul>


<a name="bnbts"></a><h4>Navigating to Single-Valued Relationship Fields</h4>
<p><a name="indexterm-1752"></a>Use the <tt>JOIN</tt> clause statement to navigate to a single-valued relationship field:</p>

<pre>SELECT t
 FROM Team t JOIN t.league l
 WHERE l.sport = &rsquo;soccer&rsquo; OR l.sport =&rsquo;football&rsquo;</pre><p>In this example, the query will return all teams that are in
either soccer or football leagues.</p>



<a name="bnbtt"></a><h4>Traversing Relationships with an Input Parameter</h4>
<pre>SELECT DISTINCT p
FROM Player p, IN (p.teams) AS t
WHERE t.city = :city</pre>
<ul><li><p><b>Data retrieved</b>: The players whose teams belong to the specified city.</p>

</li>
<li><p><a name="indexterm-1753"></a><a name="indexterm-1754"></a><b>Description</b>: This query is similar to the previous example but adds an input parameter. The <tt>AS</tt> keyword in the <tt>FROM</tt> clause is optional. In the <tt>WHERE</tt> clause, the period preceding the persistent variable <tt>city</tt> is a delimiter, not a navigation operator. Strictly speaking, expressions can navigate to relationship fields (related entities) but not to persistent fields. To access a persistent field, an expression uses the period as a delimiter.</p>

<p>Expressions cannot navigate beyond (or further qualify) relationship fields that are collections. In the syntax of an expression, a collection-valued field is a terminal symbol. Because the <tt>teams</tt> field is a collection, the <tt>WHERE</tt> clause cannot specify <tt>p.teams.city</tt> (an illegal expression).</p>

</li>
<li><p><b>See also</b>: <a href="bnbuf.html#bnbuq">Path Expressions</a>.</p>

</li></ul>


<a name="bnbtu"></a><h4>Traversing Multiple Relationships</h4>
<pre>SELECT DISTINCT p
FROM Player p, IN (p.teams) t
WHERE t.league = :league</pre>
<ul><li><p><b>Data retrieved</b>: The players who belong to the specified league.</p>

</li>
<li><p><a name="indexterm-1755"></a><b>Description</b>: The expressions in this query navigate over two relationships. The <tt>p.teams</tt> expression navigates the <tt>Player</tt>-<tt>Team</tt> relationship, and the <tt>t.league</tt> expression navigates the <tt>Team</tt>-<tt>League</tt> relationship.</p>

</li></ul>
<p>In the other examples, the input parameters are <tt>String</tt> objects; in this example,
the parameter is an object whose type is a <tt>League</tt>. This type matches
the <tt>league</tt> relationship field in the comparison expression of the <tt>WHERE</tt> clause.</p>



<a name="bnbtv"></a><h4>Navigating According to Related Fields</h4>
<pre>SELECT DISTINCT p
FROM Player p, IN (p.teams) t
WHERE t.league.sport = :sport</pre>
<ul><li><p><b>Data retrieved</b>: The players who participate in the specified sport.</p>

</li>
<li><p><a name="indexterm-1756"></a><b>Description</b>: The <tt>sport</tt> persistent field belongs to the <tt>League</tt> entity. To reach the <tt>sport</tt> field, the query must first navigate from the <tt>Player</tt> entity to <tt>Team</tt> (<tt>p.teams</tt>) and then from <tt>Team</tt> to the <tt>League</tt> entity (<tt>t.league</tt>). Because it is not a collection, the <tt>league</tt> relationship field can be followed by the <tt>sport</tt> persistent field.</p>

</li></ul>


<a name="bnbtw"></a><h3>Queries with Other Conditional Expressions</h3>
<p><a name="indexterm-1757"></a>Every <tt>WHERE</tt> clause must specify a conditional expression, of which there are several
kinds. In the previous examples, the conditional expressions are comparison expressions that test for
equality. The following examples demonstrate some of the other kinds of conditional expressions.
For descriptions of all conditional expressions, see <a href="bnbuf.html#bnbuu"><tt>WHERE</tt> Clause</a>.</p>



<a name="bnbtx"></a><h4>The <tt>LIKE</tt> Expression</h4>
<a name="indexterm-1758"></a><pre>SELECT p
 FROM Player p
 WHERE p.name LIKE &rsquo;Mich%&rsquo;</pre>
<ul><li><p><b>Data retrieved</b>: All players whose names begin with &ldquo;Mich.&rdquo;</p>

</li>
<li><p><b>Description</b>: The <tt>LIKE</tt> expression uses wildcard characters to search for strings that match the wildcard pattern. In this case, the query uses the <tt>LIKE</tt> expression and the <tt>%</tt> wildcard to find all players whose names begin with the string &ldquo;Mich.&rdquo; For example, &ldquo;Michael&rdquo; and &ldquo;Michelle&rdquo; both match the wildcard pattern.</p>

</li>
<li><p><b>See also</b>: <a href="bnbuf.html#bnbvg"><tt>LIKE</tt> Expressions</a>.</p>

</li></ul>


<a name="bnbty"></a><h4>The <tt>IS NULL</tt> Expression</h4>
<a name="indexterm-1759"></a><pre>SELECT t
 FROM Team t
 WHERE t.league IS NULL</pre>
<ul><li><p><b>Data retrieved</b>: All teams not associated with a league.</p>

</li>
<li><p><b>Description</b>: The <tt>IS NULL</tt> expression can be used to check whether a relationship has been set between two entities. In this case, the query checks whether the teams are associated with any leagues and returns the teams that do not have a league.</p>

</li>
<li><p><b>See also</b>: <a href="bnbuf.html#bnbvi"><tt>NULL</tt> Comparison Expressions</a> and <a href="bnbuf.html#bnbvr">NULL Values</a>.</p>

</li></ul>


<a name="bnbtz"></a><h4>The <tt>IS EMPTY</tt> Expression</h4>
<a name="indexterm-1760"></a><pre>SELECT p
FROM Player p
WHERE p.teams IS EMPTY</pre>
<ul><li><p><b>Data retrieved</b>: All players who do not belong to a team.</p>

</li>
<li><p><b>Description</b>: The <tt>teams</tt> relationship field of the <tt>Player</tt> entity is a collection. If a player does not belong to a team, the <tt>teams</tt> collection is empty, and the conditional expression is <tt>TRUE</tt>.</p>

</li>
<li><p><b>See also</b>: <a href="bnbuf.html#bnbvj">Empty Collection Comparison Expressions</a>.</p>

</li></ul>


<a name="bnbua"></a><h4>The <tt>BETWEEN</tt> Expression</h4>
<a name="indexterm-1761"></a><pre>SELECT DISTINCT p
FROM Player p
WHERE p.salary BETWEEN :lowerSalary AND :higherSalary</pre>
<ul><li><p><b>Data retrieved</b>: The players whose salaries fall within the range of the specified salaries.</p>

</li>
<li><p><b>Description</b>: This <tt>BETWEEN</tt> expression has three arithmetic expressions: a persistent field (<tt>p.salary</tt>) and the two input parameters (<tt>:lowerSalary</tt> and <tt>:higherSalary</tt>). The following expression is equivalent to the <tt>BETWEEN</tt> expression:</p>

<pre>p.salary >= :lowerSalary AND p.salary &lt;= :higherSalary</pre></li>
<li><p><b>See also</b>: <a href="bnbuf.html#bnbve"><tt>BETWEEN</tt> Expressions</a>.</p>

</li></ul>


<a name="bnbub"></a><h4>Comparison Operators</h4>
<a name="indexterm-1762"></a><pre>SELECT DISTINCT p1
FROM Player p1, Player p2
WHERE p1.salary > p2.salary AND p2.name = :name</pre>
<ul><li><p><b>Data retrieved</b>: All players whose salaries are higher than the salary of the player with the specified name.</p>

</li>
<li><p><b>Description</b>: The <tt>FROM</tt> clause declares two identification variables (<tt>p1</tt> and <tt>p2</tt>) of the same type (<tt>Player</tt>). Two identification variables are needed because the <tt>WHERE</tt> clause compares the salary of one player (<tt>p2</tt>) with that of the other players (<tt>p1</tt>).</p>

</li>
<li><p><b>See also</b>: <a href="bnbuf.html#bnbum">Identification Variables</a>.</p>

</li></ul>


<a name="bnbuc"></a><h3>Bulk Updates and Deletes</h3>
<p><a name="indexterm-1763"></a><a name="indexterm-1764"></a>The following examples show how to use the <tt>UPDATE</tt> and <tt>DELETE</tt> expressions in queries.
<tt>UPDATE</tt> and <tt>DELETE</tt> operate on multiple entities according to the condition or conditions
set in the <tt>WHERE</tt> clause. The <tt>WHERE</tt> clause in <tt>UPDATE</tt> and <tt>DELETE</tt>
queries follows the same rules as <tt>SELECT</tt> queries.</p>



<a name="bnbud"></a><h4>Update Queries</h4>
<a name="indexterm-1765"></a><pre>UPDATE Player p
SET p.status = 'inactive'
WHERE p.lastPlayed &lt; :inactiveThresholdDate</pre>
<ul><li><p><b>Description</b>: This query sets the status of a set of players to <tt>inactive</tt> if the player&rsquo;s last game was longer than the date specified in <tt>inactiveThresholdDate</tt>.</p>

</li></ul>


<a name="bnbue"></a><h4>Delete Queries</h4>
<a name="indexterm-1766"></a><pre>DELETE
FROM Player p
WHERE p.status = 'inactive'
AND p.teams IS EMPTY</pre>
<ul><li><p><b>Description</b>: This query deletes all inactive players who are not on a team.</p>

</li></ul>

         </div>
         <div class="navigation">
             <a href="bnbti.html"><img src="graphics/leftButton.gif" border="0" alt="Previous" title="Previous"></a>
             <a href="p1.html"><img src="graphics/upButton.gif" border="0" alt="Contents" title="Contents"></a>
             <a href="bnbuf.html"><img src="graphics/rightButton.gif" border="0" alt="Next" title="Next"></a>
         </div>

         <div class="copyright">
      	    <p>Copyright &copy; 2011, Oracle and/or its affiliates. All rights reserved. <a href="docinfo.html">Legal Notices</a></p>
      	 </div>

      </td>
   </tr>
</tbody>
</table>
</body>
</html>

